#coding=utf-8
#@Time:2020/3/24 1:48 PM
#@Author:希哥
#@File:fix_order.py

import sql
import requests
import time
import datetime
import dingding
from configs.logs import run_logs as log
logging = log.get_logger()
s = requests.session()

'''
输入订单号，修复原本为支付成功的订单
'''
def fix(operator, *args):
    now = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime(time.time()))  # 数据产生日期
    for i in range(0,len(args)):
        order_no = args[i]
        if order_no == '':
            return '请输入订单号'
        else:
            check_sql = "SELECT id FROM orders WHERE order_no = '"+order_no+"'"
            orderId = sql.search(check_sql, 'hupo_online')
            if orderId == 'None':
                msg = '任务提示：' + str(now) + '，订单号：' + order_no + '修复无效订单，无此订单数据，请检查所填内容，操作人：' + str(operator)
                dingding.DingTalk_Base().send_msg(msg)
                return msg
            else:
                update_sql_01 = "UPDATE payment_record SET state = 'success' WHERE order_id = (SELECT id FROM orders WHERE order_no = '"+order_no+"')"
                update_sql_02 = "UPDATE orders SET state = 'payment_finish',payment_state = 'success' WHERE order_no = '"+order_no+"'"
                user_id_sql = "SELECT user_id FROM orders WHERE order_no = '"+order_no+"'"
                '''
                修改payment_record表中的状态为success
                '''
                sql.update(update_sql_01,'hupo_online')
                '''
                修改orders表中的状态为payment_finish和success
                '''
                sql.update(update_sql_02,'hupo_online')
                '''
                获取用户的user_id
                '''
                user_id = str(sql.search(user_id_sql,'hupo_online'))
                '''
                清除用户的缓存
                '''
                remove_user = s.get('https://api.hupovip.cn/hp-mall1.1.0/v1/systemTool/remove/order/userId?userId='+user_id)
                '''
                执行接口，修复订单
                '''
                fixOrder = s.get('https://api.hupovip.cn/hp-mall1.1.0/v1/systemTool/save?orderId='+orderId)
                '''
                写入记录
                '''
                write_sql = "insert into xige_hp_test.order_fix_record(create_time,order_no,operator_type,operator,result) values('%s','%s','%s','%s','%s')" % (
                now, order_no, '修复无效订单', operator, fixOrder.text)
                sql.insert(write_sql, 'hupo_online')
                '''
                    钉钉消息通知
                '''
                msg = '任务提示：' + str(now) + '，订单号：' + order_no + '修复无效订单'+ str(fixOrder.text) +'，操作人：' + str(operator)
                dingding.DingTalk_Base().send_msg(msg)
                return msg


'''
输入订单号，修复原本为支付成功的订单
'''
def fix_groupbuying(operator, *args):
    now = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime(time.time()))  # 数据产生日期
    for i in range(0,len(args)):
        order_no = args[i]
        if order_no == '':
            return '请输入订单号'
        else:
            check_sql = "SELECT id FROM orders WHERE order_no = '"+order_no+"'"
            orderId = sql.search(check_sql, 'hupo_online')
            if orderId == 'None':
                msg = '任务提示：' + str(now) + '，订单号：' + order_no + '修复无效订单，无此订单数据，请检查所填内容，操作人：' + str(operator)
                dingding.DingTalk_Base().send_msg(msg)
                return msg
            else:
                '''
                    执行接口，修复订单
                '''
                fixOrder = s.get('https://mall-admin.hupovip.cn/hp-mall-admin/v1/activity/forceEndGroupBuying?orderNo=' + orderId)
                '''
                写入记录
                '''
                write_sql = "insert into xige_hp_test.order_fix_record(create_time,order_no,operator_type,operator,result) values('%s','%s','%s','%s','%s')" % (now, order_no, '修复拼团订单', operator, fixOrder.text)
                sql.insert(write_sql, 'hupo_online')
                '''
                    钉钉消息通知
                '''
                msg = '任务提示：' + str(now) + '，订单号：' + order_no + '，修复拼团订单' + str(fixOrder.text) + '，操作人：' + str(operator)
                dingding.DingTalk_Base().send_msg(msg)
                return msg

'''
输入订单号，作废凭证码
'''
def delete_certificate(operator, *args):
    now = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime(time.time()))  # 数据产生日期
    for i in range(0,len(args)):
        now = datetime.datetime.now()
        # 获取今天零点
        zeroToday = now - datetime.timedelta(hours=now.hour, minutes=now.minute, seconds=now.second,microseconds=now.microsecond)
        order_no = args[i]
        if order_no == '':
            return '请输入订单号'
        else:
            updata_sql_01 = "UPDATE biz_certificate SET `status` = 'expired',end_time = '"+ str(zeroToday) +"' WHERE from_id in (SELECT id FROM orders WHERE order_no in ('"+order_no+"'));"
            updata_sql_02 = "UPDATE biz_certificate_group SET `status` = 'expired',end_time = '"+ str(zeroToday) +"' WHERE from_id in (SELECT id FROM orders WHERE order_no in ('"+order_no+"'));"
            '''
            修改凭证码为过期
            '''
            sql.update(updata_sql_01,'hupo_online')
            '''
            修改凭证组过期
            '''
            sql.update(updata_sql_02,'hupo_online')
            '''
            写入记录
            '''
            write_sql = "insert into xige_hp_test.order_fix_record(create_time,order_no,operator_type,operator,result) values('%s','%s','%s','%s','%s')" % (
                now, order_no, '作废凭证', operator, '')
            sql.insert(write_sql, 'hupo_online')
            '''
                钉钉消息通知
            '''
            msg = '任务提示：' + str(now) + '，订单号：' + order_no + '作废全部凭证成功，操作人：' + str(operator)
            dingding.DingTalk_Base().send_msg(msg)
            return str(order_no) + u'作废成功！'

'''
    增加、减少用户的会员时长
'''
def delete_member(operator, mobile, time):
    now = datetime.datetime.now()
    if mobile == '':
        return '请输入手机号码'
    if time == '':
        return '请输入天数'
    else:
        time = int(time)
        check_memberExpireTime_sql = "SELECT member_expire_time FROM `user` WHERE mobile = '"+ mobile +"' AND user_type = 'normal' AND dt = 0;"
        memberExpireTime = sql.search(check_memberExpireTime_sql, 'hupo_online')
        if memberExpireTime == 'None':
            msg = '任务提示：' + str(now) + '用户：' + mobile + '不存在，请检查所填内容，操作人：' + str(operator)
            '''
                钉钉消息通知
            '''
            dingding.DingTalk_Base().send_msg(msg)
            return msg
        date_memberTime = datetime.datetime.strptime(memberExpireTime, '%Y-%m-%d %H:%M:%S')
        '''
            增加、减少用户的会员时间，增加为正数，减少为负数,单位为"天"
        '''
        new_memberTime = date_memberTime + datetime.timedelta(days=time)
        update_sql = "UPDATE `user` SET member_expire_time = '"+ str(new_memberTime) +"' WHERE mobile = '"+ mobile +"' AND user_type = 'normal' AND dt = 0;"
        sql.update(update_sql, 'hupo_online')
        '''
            再判断新的会员到期时间和开始时间，如果到期时间小于当前时间，则将is_member = -1
        '''
        now = datetime.datetime.now()
        # 获取今天零点
        zeroToday = now - datetime.timedelta(hours=now.hour, minutes=now.minute, seconds=now.second,microseconds=now.microsecond)
        # 获取23:59:59
        lastToday = zeroToday + datetime.timedelta(hours=23, minutes=59, seconds=59)
        if new_memberTime < lastToday:
            update_state_sql = "UPDATE `user` SET is_member = '-1' WHERE mobile = '" + mobile + "' AND user_type = 'normal' AND dt = 0;"
            sql.update(update_state_sql, 'hupo_online')
            msg = '任务提示：' + str(now) + '用户：' + mobile + '，原会员到期时间为：' + str(memberExpireTime) + '，会员到期时间已调整为：' + str(new_memberTime) + '，因调整后的到期日期小于当前日期，故会员状态已调整为过期会员，操作人：' + str(operator)
            '''
                写入记录
            '''
            write_sql = "insert into xige_hp_test.order_fix_record(create_time,order_no,operator_type,operator,result) values('%s','%s','%s','%s','%s')" % (now, mobile, '调整会员时长', operator, msg)
            sql.insert(write_sql, 'hupo_online')
            '''
                钉钉消息通知
            '''
            dingding.DingTalk_Base().send_msg(msg)
            return msg
        else:
            update_state_sql = "UPDATE `user` SET is_member = '1' WHERE mobile = '" + mobile + "' AND user_type = 'normal' AND dt = 0;"
            sql.update(update_state_sql, 'hupo_online')
            '''
                写入记录
            '''
            write_sql = "insert into xige_hp_test.order_fix_record(create_time,order_no,operator_type,operator,result) values('%s','%s','%s','%s','%s')" % (now, mobile, '调整会员时长', operator,'用户：' + mobile + '会员到期时间已调整为：' + str(new_memberTime))
            sql.insert(write_sql, 'hupo_online')
            '''
                钉钉消息通知
            '''
            msg = '任务提示：' + str(now) + '用户：' + mobile + '，原会员到期时间为：' + str(memberExpireTime) + '，会员到期时间已调整为：' + str(new_memberTime) + ',操作人：' + str(operator)
            dingding.DingTalk_Base().send_msg(msg)
            return msg


'''
    亲子卡退款流程
'''
def qinzika_refund(operator, mobile, type, date):
    now = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime(time.time()))  # 数据产生日期
    if mobile == '':
        return '请输入手机号码'
    if type == '':
        return '请输入亲子卡关键字'
    if date == '':
        return '请输入扣减会员天数'
    else:
        '''
            获取该会员包中的所有优惠券code
        '''
        coupon_code_sql = "SELECT ctu.coupon_code FROM coupon_to_user as ctu INNER JOIN hp_gift_pack.gift_pack_to_gift as gptp ON ctu.coupon_config_id = gptp.gift_id INNER JOIN hp_gift_pack.gift_pack as gp ON gp.id = gptp.gift_pack_id WHERE gp.title = '"+ type +"' AND ctu.user_id = (SELECT id FROM `user` WHERE mobile = '"+ mobile +"' AND user_type = 'normal') GROUP BY ctu.coupon_code"
        coupon_code_list = sql.search_total(coupon_code_sql , 'hupo_online')
        if coupon_code_list == []:
            msg = '任务提示：' + str(now) + '用户：' + mobile + '的' + type + '会员包的优惠券无数据,操作人：' + str(operator)
            dingding.DingTalk_Base().send_msg(msg)
            return msg
        else:
            for i in range(0,len(coupon_code_list)):
                coupon_code = str(coupon_code_list[i][0])
                '''
                    调用作废优惠券函数
                '''
                delete_coupon(operator, coupon_code)
            '''
                作废会员**天
            '''
            date = '-' + date
            delete_member(operator, mobile, date)
            msg = '任务提示：' + str(now) + '用户：' + mobile + '的' + type + '会员包的优惠券均已作废,操作人：' + str(operator)
            dingding.DingTalk_Base().send_msg(msg)
            return msg

'''
    作废单张优惠券
'''
def delete_coupon(operator, coupon_code):
    now = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime(time.time()))  # 数据产生日期
    if coupon_code == '':
        return '请输入优惠券编码'
    else:
        '''
            先查询该优惠券的状态，若为待使用则作废，否则不作废返回状态
        '''
        check_coupon_sql = "SELECT state FROM coupon_to_user WHERE coupon_code = '"+ coupon_code +"'"
        coupon_state = sql.search(check_coupon_sql, 'hupo_online')
        if coupon_state == 'WAIT_USE':
            delete_sql = "UPDATE coupon_to_user SET state = 'TIME_OUT',valid_date = DATE_FORMAT(CURDATE(),'%Y-%m-%d %H:%i:%s') WHERE coupon_code = '"+ coupon_code +"';"
            sql.update(delete_sql, 'hupo_online')
            '''
                写入记录
            '''
            write_sql = "insert into xige_hp_test.order_fix_record(create_time,order_no,operator_type,operator,result) values('%s','%s','%s','%s','%s')" % (
            now, coupon_code, '单张优惠券作废', operator, '优惠券编码：' + coupon_code + '已作废成功')
            sql.insert(write_sql, 'hupo_online')
            '''
                钉钉消息通知
            '''
            msg = '任务提示：' + str(now) + '优惠券编码：' + coupon_code + '已作废,操作人：' + str(operator)
            dingding.DingTalk_Base().send_msg(msg)
            return '优惠券编码：' + coupon_code + '已作废成功'
        if coupon_state == 'None':
            msg = '优惠券编码：' + coupon_code + '不存在,操作人：' + str(operator)
            dingding.DingTalk_Base().send_msg(msg)
            return msg
        else:
            '''
                写入记录
            '''
            write_sql = "insert into xige_hp_test.order_fix_record(create_time,order_no,operator_type,operator,result) values('%s','%s','%s','%s','%s')" % (
                now, coupon_code, '单张优惠券作废', operator, '优惠券编码：' + coupon_code + '状态为：' + str(coupon_state) + '无需作废')
            sql.insert(write_sql, 'hupo_online')
            '''
                钉钉消息通知
            '''
            msg = '任务提示：' + str(now) + '优惠券编码：' + coupon_code + '状态为：' + str(coupon_state) + '无需作废,操作人：' + str(operator)
            dingding.DingTalk_Base().send_msg(msg)
            return '优惠券编码：' + coupon_code + '状态为：' + str(coupon_state) + '无需作废'

'''
    作废会员包兑换码
'''
def delete_gift_code(operator, code):
    now = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime(time.time()))  # 数据产生日期
    if code == '':
        return '请输入兑换码'
    else:
        '''
            检查兑换码是否已兑换，若未兑换则作废
        '''
        check_sql = "SELECT CASE gprc.state WHEN 'INACTIVATION' THEN '未激活' WHEN 'NORMAL' THEN '正常' WHEN 'INVALID' THEN '失效' WHEN 'EXPIRE' THEN '过期' END '兑换码状态',CASE gprr.state WHEN 'INACTIVATION' THEN '未激活' WHEN 'RECEIVED' THEN '已兑换' WHEN 'INVALID' THEN '失效' END '是否已经兑换' FROM hp_gift_pack.gift_pack_receive_record as gprr JOIN hp_gift_pack.gift_pack_receive_code as gprc ON gprr.id = gprc.gift_pack_receive_id LEFT JOIN hp_gift_pack.git_pack_open_gift_user as gpogu ON gpogu.gift_pack_receive_id = gprr.id WHERE gprc.`code` = '"+ code +"';"
        result = sql.search_total(check_sql, 'hupo_online')
        state_1 = result[0][0]
        state_2 = result[0][1]
        if state_1 == u'过期':
            msg = '任务提示：' + str(now) + '，会员包兑换码作废，兑换码：' + code + '已过期，无需作废，操作人：' + str(operator)
            dingding.DingTalk_Base().send_msg(msg)
            return msg
        if state_2 == u'已兑换':
            msg = '任务提示：' + str(now) + '，会员包兑换码作废，兑换码：' + code + '已兑换，无需作废，操作人：' + str(operator)
            dingding.DingTalk_Base().send_msg(msg)
            return msg
        else:
            update_sql = "UPDATE hp_gift_pack.gift_pack_receive_code SET state = 'EXPIRE',end_time = DATE_FORMAT(CURDATE(),'%Y-%m-%d %H:%i:%s') WHERE `code` = '"+ code +"';"
            sql.update(update_sql, 'hupo_online')
            msg = '任务提示：' + str(now) + '，会员包兑换码作废，兑换码：' + code + '已作废，操作人：' + str(operator)
            '''
                写入记录
            '''
            write_sql = "insert into xige_hp_test.order_fix_record(create_time,order_no,operator_type,operator,result) values('%s','%s','%s','%s','%s')" % (
                now, code, '会员包兑换码作废', operator, msg)
            sql.insert(write_sql, 'hupo_online')
            dingding.DingTalk_Base().send_msg(msg)
            return msg


'''
    修改用户为新用户
'''
def change_new_user(operator, mobile):
    now = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime(time.time()))  # 数据产生日期
    if mobile == '':
        return '请输入手机号码'
    else:
        '''
            检查用户是否为新用户
        '''
        check_sql = "SELECT o.order_no FROM orders as o LEFT JOIN `user` as u ON o.user_id = u.id WHERE u.mobile = '"+ mobile +"' AND o.dt = 0 AND u.user_type = 'normal' AND u.dt = 0"
        result = sql.search(check_sql ,'hupo_online')
        if result == 'None':
            msg = '任务提示：' + str(now) + '用户：' + mobile + '是新用户，无需处理，操作人：' + str(operator)
            '''
                写入记录
            '''
            write_sql = "insert into xige_hp_test.order_fix_record(create_time,order_no,operator_type,operator,result) values('%s','%s','%s','%s','%s')" % (
                now, mobile, '修改用户为新用户', operator, msg)
            sql.insert(write_sql, 'hupo_online')
            dingding.DingTalk_Base().send_msg(msg)
            return msg
        else:
            update_sql = "UPDATE orders SET dt = 1 WHERE user_id = (SELECT id FROM `user` WHERE mobile = '"+ mobile +"' AND user_type = 'normal' AND dt = 0)"
            userId_sql = "SELECT id FROM `user` WHERE mobile = '"+ mobile +"' AND user_type = 'normal' AND dt = 0"
            user_id = sql.search(userId_sql , 'hupo_online')
            sql.update(update_sql, 'hupo_online')
            '''
                清除用户的缓存
            '''
            remove_user = s.get('https://api.hupovip.cn/hp-mall1.1.0/v1/systemTool/remove/order/userId?userId='+user_id)
            msg = '任务提示：' + str(now) + '用户：' + mobile + '不是新用户,现已修改为新用户成功，操作人：' + str(operator)
            '''
                写入记录
            '''
            write_sql = "insert into xige_hp_test.order_fix_record(create_time,order_no,operator_type,operator,result) values('%s','%s','%s','%s','%s')" % (
                now, mobile, '修改用户为新用户', operator, msg)
            sql.insert(write_sql, 'hupo_online')
            dingding.DingTalk_Base().send_msg(msg)
            return msg


'''
    用户退款后手动返回对应用户的优惠券
'''
def return_coupon(operator, mobile):
    now = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime(time.time()))  # 数据产生日期
    if mobile == '':
        return '请输入手机号码'
    else:
        userId_sql = "SELECT id FROM `user` WHERE mobile = '" + mobile + "' AND user_type = 'normal' AND dt = 0"
        user_id = sql.search(userId_sql, 'hupo_online')
        '''
            执行返回优惠券接口
        '''
        return_coupon_request = s.get('https://mall-admin.hupovip.cn/hp-mall-admin/test/refundCoupon/'+user_id)
        msg = '任务提示：' + str(now) + '用户：' + mobile + '，手动返回用户已退款的优惠券'+'，操作人：' + str(operator)
        '''
            写入记录
        '''
        write_sql = "insert into xige_hp_test.order_fix_record(create_time,order_no,operator_type,operator,result) values('%s','%s','%s','%s','%s')" % (
            now, mobile, '手动返回用户已退款的优惠券', operator, '')
        sql.insert(write_sql, 'hupo_online')
        dingding.DingTalk_Base().send_msg(msg)
        return return_coupon_request.text
if __name__=='__main__':
    print return_coupon('希哥','17781108472')